<?php

class ecrmGenerateReportTask extends sfBaseTask
{
  protected function configure()
  {
    // // add your own arguments here
    // $this->addArguments(array(
    //   new sfCommandArgument('my_arg', sfCommandArgument::REQUIRED, 'My argument'),
    // ));

    $this->addOptions(array(
      new sfCommandOption('application', null, sfCommandOption::PARAMETER_REQUIRED, 'The application name'),
      new sfCommandOption('env', null, sfCommandOption::PARAMETER_REQUIRED, 'The environment', 'dev'),
      new sfCommandOption('connection', null, sfCommandOption::PARAMETER_REQUIRED, 'The connection name', 'doctrine'),
      // add your own options here
    ));

    $this->namespace        = 'ecrm';
    $this->name             = 'generateReport';
    $this->briefDescription = '';
    $this->detailedDescription = <<<EOF
The [ecrm:generateReport|INFO] task does things.
Call it with:

  [php symfony ecrm:generateReport|INFO]
EOF;
  }

  protected function execute($arguments = array(), $options = array())
  {
    // initialize the database connection
    $databaseManager = new sfDatabaseManager($this->configuration);
    $connection = $databaseManager->getDatabase($options['connection'])->getConnection();

    // add your code here
    $fileName = getcwd().'/web/campaignsReport-'.date('YmdHis').'.txt';
    $campaigns = Doctrine_Core::getTable('EmailCampaigns')->createQuery()->orderBy('short_label')->execute(array(), Doctrine_Core::HYDRATE_ARRAY);
    
    $line = "\t";
    foreach($campaigns as $key=>$campaign) {
      $line .= $campaign['short_label'];
      $line .= "\t\t\t\t\t\t\t\t";
    }
    $line .= 'total';
    $line .= "\t\t\t\t\t\t\t\t";
    file_put_contents($fileName, $line."\n");
    $line = 'email'."\t";
    foreach($campaigns as $campaign) {
      $line .= 'selected'."\t".'hard_bounce'."\t".'soft_bounce'."\t".'received'."\t".'opened'."\t".'clicked'."\t".'unsubscribed'."\t".'complaint'."\t";
    }
    $line .= 'selected'."\t".'hard_bounce'."\t".'soft_bounce'."\t".'received'."\t".'opened'."\t".'clicked'."\t".'unsubscribed'."\t".'complaint'."\t";
    file_put_contents($fileName, $line."\n", FILE_APPEND);
    
    $manager = Doctrine_Manager::getInstance();
    $conn = $manager->getCurrentConnection();
    
    $offset = 0;
    $rowsPerQuery = 5000;
    do {
      $emailsSql = 'SELECT DISTINCT email FROM email_campaign_user_performance ORDER BY id LIMIT '.$offset.','.$rowsPerQuery;
      $this->log('start getting email query '.$offset);
      $emailsResult = $conn->execute($emailsSql)->fetchAll(PDO::FETCH_ASSOC);
      $rowCount = count($emailsResult);
      if($rowCount > 0) {
        $offset += $rowsPerQuery;
        foreach($emailsResult as $email) {
          $line = $email['email']."\t";
          $totals = array(
            'selected' => 0,
            'hard_bounce' => 0,
            'soft_bounce' => 0,
            'received' => 0,
            'opened' => 0,
            'clicked' => 0,
            'unsubscribed' => 0,
            'complaint' => 0,
          );
          $campaignPerformanceQuery = '';
          foreach($campaigns as $key=>$campaign) {
            $campaignPerformanceQuery .= 'SELECT * FROM email_campaign_user_performance WHERE email = "'.$email['email'].'" AND campaign_short_label = "'.$campaign['short_label'].'"';
            if($key != (count($campaigns)-1)) {
              $campaignPerformanceQuery .= ' UNION ';
            } 
          }
          //$this->log('start getting stats for '.$email['email']);
          $campaignPerformanceResult = $conn->execute($campaignPerformanceQuery)->fetchAll(PDO::FETCH_ASSOC);
          $campaignCount = count($campaignPerformanceResult);
          $campaignCounter = 0;
          foreach($campaignPerformanceResult as $campaignPerformance) {
            $campaignCounter += 1;
            foreach(array_keys($totals) as $reaction) {
              if($campaignPerformance[$reaction] == 'Yes') {
                $line .= '1';
                $totals[$reaction] += 1;
              }
              else {
                $line .= '0';
              }
              $line .= "\t";
            }
          }
          $remainingCampaigns = count($campaigns)-$campaignCounter;
          if($remainingCampaigns > 0) {
            for($i = 0; $i < $remainingCampaigns; $i++) {
              $line .= "0\t0\t0\t0\t0\t0\t0\t0\t";
            }
          }
          foreach($totals as $total) {
            $line .= $total."\t";
          }
          file_put_contents($fileName, $line."\n", FILE_APPEND);
        }
      }  
    }
    while ($rowCount > 0);
  }
}
